import numpy as np
import pandas as pd
from pandas import DataFrame

from PD.PdUtil import count_values_appearing_more_than_twice


def match(row, data):
    print(f'处理小区{row["小区名称"]}，日期{row["隐患问题发现日期"]}')
    date = row['隐患问题发现日期']
    date_complete = row[t1]
    cell_name = row['小区名称']
    date2 = row['前2月时间']
    date3 = row['前3月时间']
    date4 = row['前4月时间']
    date5 = row['后1月时间']
    res = []
    filter = data[data['所属小区'] == cell_name]
    data2: DataFrame = filter.loc[(filter['happen_time'] >= date4) &
                                  (filter['happen_time'] <= date3 + pd.DateOffset(days=1))]
    res.append(len(data2.drop_duplicates(subset=['account'])))
    data3: DataFrame = filter.loc[(filter['happen_time'] >= date3) &
                                  (filter['happen_time'] <= date2 + pd.DateOffset(days=1))]
    res.append(len(data3.drop_duplicates(subset=['account'])))
    data4 = filter.loc[(filter['happen_time'] >= date2) &
                       (filter['happen_time'] <= date + pd.DateOffset(days=1))]
    res.append(len(data4.drop_duplicates(subset=['account'])))
    res.append(count_values_appearing_more_than_twice(data2, 'account'))
    res.append(count_values_appearing_more_than_twice(data3, 'account'))
    res.append(count_values_appearing_more_than_twice(data4, 'account'))

    data2 = filter.loc[(filter['happen_time'] >= date_complete) &
                       (filter['happen_time'] <= date5 + pd.DateOffset(days=1))]
    res.append(len(data2.drop_duplicates(subset=['account'])))
    res.append(count_values_appearing_more_than_twice(data2, 'account'))
    return pd.Series(res)


def get_data():
    data_frames = []
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\9\\去除企宽用户.csv", usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\10月\\去除企宽用户.csv", usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\11月\\去除企宽用户.csv", usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\1201_1231\\去除企宽用户.csv",
                    usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\0101_0131\\去除企宽用户.csv",
                    usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\0201_0228\\去除企宽用户.csv",
                    usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\0301_0331\\去除企宽用户.csv",
                    usecols=["所属小区", "account", "happen_time"]))
    data_frames.append(
        pd.read_csv("D:\\temp\\radius数据\\0401_0430\\去除企宽用户.csv",
                    usecols=["所属小区", "account", "happen_time"]))
    merged_df = pd.concat(data_frames)
    return merged_df


def handler():
    data = get_data()
    print("读取中断数据完成")

    data["happen_time"] = pd.to_datetime(data["happen_time"])

    # df = pd.read_excel("D:\\temp\\新TOP200(1).xlsx")
    df = pd.read_excel("D:\\家宽\\调研记录表.xlsx")
    df["前2月时间"] = df['隐患问题发现日期'] - pd.DateOffset(months=1)
    df["前3月时间"] = df['前2月时间'] - pd.DateOffset(months=1)
    df["前4月时间"] = df['前3月时间'] - pd.DateOffset(months=1)
    # 用发现日期填充整改日期
    df[t1] = df[t1].fillna(df['隐患问题发现日期'])
    df[t1] = pd.to_datetime(df[t1], errors='coerce')
    df["后1月时间"] = df[t1] + pd.DateOffset(months=1)
    df[['前3', '前2', '前1',
        '前3重复中断', '前2重复中断', '前1重复中断',
        '后一', '后一重复中断']] = df.apply(match,
                                            data=data,
                                            axis=1)
    df.to_excel("/temp/调研.xlsx", index=False)


def 处理前三():
    df = pd.read_excel("/temp/调研.xlsx")
    df.rename(columns={
        '小区名称': '所属小区'
    }, inplace=True)
    df['隐患问题发现日期'] = pd.to_datetime(df['隐患问题发现日期'])
    df['前一个月'] = (df['隐患问题发现日期'].dt.to_period('M') - 1).astype(str)
    df['前两个月'] = (df['隐患问题发现日期'].dt.to_period('M') - 2).astype(str)
    df['前三个月'] = (df['隐患问题发现日期'].dt.to_period('M') - 3).astype(str)
    df2 = pd.read_excel("D:\家宽\小区中断人数.xlsx")
    cols = df2.columns[1:]
    # 使用 pd.melt() 进行转换
    melted_df = pd.melt(df2, id_vars=['所属小区'], value_vars=cols,
                        var_name='中断', value_name='t1')

    prefixes = ['人数', '重复人数']
    months = ['前三个月', '前两个月', '前一个月']
    cols = []
    for prefix in prefixes:
        for month in months:
            df['中断'] = prefix + df[month]
            cols.append(month + prefix)
            temp_melted_df = melted_df.rename(columns={'t1': month + prefix})
            df = df.merge(temp_melted_df, on=['所属小区', '中断'], how='left')

    df[cols] = df[cols].fillna(0)
    df[cols] = df[cols].astype('int64')
    # 定义条件
    condition = df['问题分类'] != '突发中断'

    # 使用loc进行批量替换
    df.loc[condition, ['前3', '前2', '前1', '前3重复中断', '前2重复中断', '前1重复中断']] = \
        df.loc[condition, cols].values

    df.to_excel('/temp/t.xlsx')


if __name__ == '__main__':
    t1 = '实际完成整改日期'
    handler()
    处理前三()
